﻿ ALTER TABLE dbo.PreContractInfo ADD
	DeliveryDate smalldatetime NULL,
	Flag		 tinyint NULL,
	IsDelay		 bit NULL
go
/****** Object:  View [dbo].[vw_PreContractInfo]    Script Date: 07/03/2009 16:33:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [dbo].[vw_PreContractInfo]
AS
SELECT     TOP (100) PERCENT dbo.PreContractInfo.PreContractID, dbo.PreContractInfo.PreContractNo, dbo.PreContractInfo.StatusID, dbo.PreContractInfo.TypeID, 
                      dbo.PreContractInfo.ProductID, dbo.PreContractInfo.PromisedDate, dbo.PreContractInfo.CEWPromised, dbo.PreContractInfo.OrionPrice, 
                      dbo.PreContractInfo.SalePrice, dbo.PreContractInfo.SignDue, dbo.PreContractInfo.SignActual, dbo.PreContractInfo.ContractName, 
                      dbo.PreContractInfo.ERPReceived, dbo.PreContractInfo.OrionNo, dbo.PreContractInfo.Delivered, dbo.PreContractInfo.CurrentSituation, 
                      dbo.PreContractInfo.BlackDate, dbo.PreContractInfo.TradeMonth AS TradeMonth_Date, MONTH(dbo.PreContractInfo.TradeMonth) AS TradeMonth, 
                      YEAR(dbo.PreContractInfo.TradeMonth) AS TradeYear, DATEPART(q, dbo.PreContractInfo.TradeMonth) AS TradeQuater, 
                      dbo.PreContractInfo.ModifiedDate, dbo.PreContractInfo.ACRNo, dbo.PreContractInfo.IsRisk, 
                      CASE dbo.PreContractInfo.ASBorLucent WHEN 0 THEN 'ASB' WHEN 1 THEN 'Lucent' END AS ASBorLucentName, dbo.PreContractInfo.Applicant, 
                      dbo.PreContractInfo.ApplicantEmail, dbo.PreContractInfo.Principal, dbo.PreContractInfo.PrincipalEmail, dbo.PreContractInfo.ProvinceID, 
                      dbo.PreContractInfo.CATID, dbo.PreContractInfo.BDID, dbo.PreContractInfo.CustomerName, dbo.PreContractInfo.CustomField01, 
                      dbo.PreContractInfo.CustomField02, dbo.PreContractInfo.CustomField03, dbo.PreContractInfo.CustomField04, dbo.PreContractInfo.DeliveryPercent, 
                      dbo.CAT.CATCode, dbo.BG.BGCode, dbo.BD.BDCode, dbo.Province.ProvinceCode, dbo.DicItem.DicItemValue AS PreContractStatus, 
                      DicItem_1.DicItemValue AS PreContractType, dbo.Product.ProductName, dbo.PreContractInfo.ASBorLucent, dbo.ExchangeRate.Rate, 
                      ROUND(dbo.PreContractInfo.CEWPromised / dbo.ExchangeRate.Rate / 1170000, 4) AS CEWME, 
                      ROUND(dbo.PreContractInfo.OrionPrice / dbo.ExchangeRate.Rate / 1170000, 4) AS OrionME, 
                      ROUND(dbo.PreContractInfo.OrionPrice / dbo.ExchangeRate.Rate / 1170000, 4) 
                      - ROUND(dbo.PreContractInfo.CEWPromised / dbo.ExchangeRate.Rate / 1170000, 4) AS GAP, dbo.vw_PreContractInfoComment_Newest.Comment, 
                      dbo.PreContractInfo.AdvancedContract, dbo.vw_PreContractInfoComment_Newest.DateCreated, dbo.Province.ProvinceCName, 
                      dbo.vw_PreContractInfoComment_Newest.Creator, dbo.vw_PreContractInfoComment_Newest.CommentPromisedDate, 
                      dbo.vw_PreContractInfoComment_Newest.Comment AS Comments, dbo.BG.BGID, 
                      CASE precontractinfo.IsRisk WHEN 0 THEN 'Secure' WHEN 1 THEN 'Risk' END AS IsRiskDesc, dbo.BD.Active AS BDActive, 
                      dbo.Province.Active AS ProvinceActive, dbo.CAT.Active AS CATActive,dbo.PreContractInfo.DeliveryDate,dbo.PreContractInfo.Flag,DATEDIFF(DAY,dbo.PreContractInfo.DeliveryDate,GETDATE())/7 as DelayWeeks,dbo.PreContractInfo.IsDelay
FROM         dbo.CAT RIGHT OUTER JOIN
                      dbo.PreContractInfo LEFT OUTER JOIN
                      dbo.vw_PreContractInfoComment_Newest ON dbo.PreContractInfo.PreContractID = dbo.vw_PreContractInfoComment_Newest.PreContractID ON 
                      dbo.CAT.CATID = dbo.PreContractInfo.CATID LEFT OUTER JOIN
                      dbo.Province ON dbo.PreContractInfo.ProvinceID = dbo.Province.ProvinceID LEFT OUTER JOIN
                      dbo.DicItem ON dbo.PreContractInfo.StatusID = dbo.DicItem.DicItemID LEFT OUTER JOIN
                      dbo.DicItem AS DicItem_1 ON dbo.PreContractInfo.TypeID = DicItem_1.DicItemID LEFT OUTER JOIN
                      dbo.Product ON dbo.PreContractInfo.ProductID = dbo.Product.ProductID LEFT OUTER JOIN
                      dbo.BG RIGHT OUTER JOIN
                      dbo.BD ON dbo.BG.BGID = dbo.BD.BGID ON dbo.PreContractInfo.BDID = dbo.BD.BDID CROSS JOIN
                      dbo.ExchangeRate
ORDER BY dbo.PreContractInfo.StatusID, dbo.PreContractInfo.PreContractNo

GO
/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvByCATByDeliveryDate]    Script Date: 07/08/2009 08:57:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Zhang wei>
-- Create date: <2009-07-07>
-- Description:	<For Get ReportValues>
-- =============================================
USE [PreContract]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvByCATByDeliveryDate]    Script Date: 07/09/2009 15:27:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Zhang wei>
-- Create date: <2009-07-07>
-- Description:	<For Get ReportValues>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetReportByProvByCATByDeliveryDate]
	@EmployeeID INT=1
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @DtToday SMALLDATETIME
	DECLARE @ExRate DECIMAL(8,6)
	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC
		SELECT a.ProvinceID,a.CATID,'0-19%' AS DeliveryType,
			COUNT(*) AS Num,
			ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
		FROM dbo.PreContractInfo a 
			INNER JOIN BD b ON a.BDID=b.BDID
			INNER JOIN CAT c ON a.CATID=c.CATID
			INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
			WHERE (a.StatusID=10 OR a.StatusID=11) 
				AND b.Active=1 AND c.Active=1 AND d.Active=1 
				AND a.DeliveryPercent>=0 AND a.DeliveryPercent<20
				--AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
			GROUP BY a.ProvinceID,a.CATID
		UNION ALL
		SELECT a.ProvinceID,a.CATID,'20-99%' AS DeliveryType,
			COUNT(*) AS Num,
			ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
		FROM dbo.PreContractInfo a 
			INNER JOIN BD b ON a.BDID=b.BDID
			INNER JOIN CAT c ON a.CATID=c.CATID
			INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
			WHERE (a.StatusID=10 OR a.StatusID=11) 
				AND b.Active=1 AND c.Active=1 AND d.Active=1 
				AND a.DeliveryPercent>=20 AND a.DeliveryPercent<100
				--AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
			GROUP BY a.ProvinceID,a.CATID
		UNION ALL
		SELECT a.ProvinceID,a.CATID,'100% Total' AS DeliveryType,
			COUNT(*) AS Num,
			ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
		FROM dbo.PreContractInfo a 
			INNER JOIN BD b ON a.BDID=b.BDID
			INNER JOIN CAT c ON a.CATID=c.CATID
			INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
			WHERE (a.StatusID=10 OR a.StatusID=11) 
				AND b.Active=1 AND c.Active=1 AND d.Active=1 
				AND a.DeliveryPercent=100
				--AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
			GROUP BY a.ProvinceID,a.CATID
		UNION ALL
		SELECT a.ProvinceID,a.CATID,'100% delivery normal' AS DeliveryType,
			COUNT(*) AS Num,
			ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
		FROM dbo.PreContractInfo a 
			INNER JOIN BD b ON a.BDID=b.BDID
			INNER JOIN CAT c ON a.CATID=c.CATID
			INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
			WHERE (a.StatusID=10 OR a.StatusID=11) 
				AND b.Active=1 AND c.Active=1 AND d.Active=1 
				AND a.DeliveryPercent=100
				AND (a.IsDelay=0 OR a.IsDelay IS NULL)
				--AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
			GROUP BY a.ProvinceID,a.CATID
		UNION ALL
			SELECT a.ProvinceID,a.CATID,'100% delivery loan/try' AS DeliveryType,
				COUNT(*) AS Num,
				ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.DeliveryPercent=100
					AND a.IsDelay=1
					--AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
			GROUP BY a.ProvinceID,a.CATID
		UNION ALL
		SELECT a.ProvinceID,a.CATID,'All Total' AS DeliveryType,
			COUNT(*) AS Num,
			ISNULL(ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0),0) AS Amount
		FROM dbo.PreContractInfo a 
			INNER JOIN BD b ON a.BDID=b.BDID
			INNER JOIN CAT c ON a.CATID=c.CATID
			INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
			WHERE (a.StatusID=10 OR a.StatusID=11) 
				AND b.Active=1 AND c.Active=1 AND d.Active=1 
				--AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
			GROUP BY a.ProvinceID,a.CATID	
	SET NOCOUNT OFF;
END
GO
--菜单
INSERT INTO MenuConfig 
(MenuTitle,MenuTitle_CN,MenuURL,ParentMenuID,MenuSequenceID,IsEnabled,[Target],MenuGroup)
VALUES
('Delivery Report','发货报表统计','Report/ByProvinceByCatByDeliveryDate.aspx',51,301,1,'main',NULL)
go
INSERT  MenuRoleMapping
select max(MenuId),1 from MenuConfig
go
INSERT  MenuRoleMapping
select max(MenuId),2 from MenuConfig
go
INSERT  MenuRoleMapping
select max(MenuId),3 from MenuConfig
go